insert overwrite table jms_dm.dm_pre_reach_rate_notrace_dt partition(dt)
select detail.network_code,
       detail.agent_code,
       net.virt_code,
       detail.port_type,
       detail.network_name,
       detail.agent_name,
       net.virt_name,
       reach_cnt,
       all_cnt,
       date(dt) statistic_date,
       dt
from (
SELECT start_pick_network_name              as network_name
     , start_pick_network_code              as network_code
     , start_subordinate_agent_code         as agent_code
     , start_subordinate_agent_name         as agent_name
     , 2                                  as port_type ---基础指标
     , SUM(cainiao_pres_platform_reach_sum) as reach_cnt
     , SUM(cainiao_pres_platform_total)     as all_cnt
     , date(plan_cainiao_platform_date)     as dt
from jms_dm.dm_net_pre_reach_rate_plan_dt
where date(plan_cainiao_platform_date) = '{{ execution_date | cst_ds }}'
  and dt BETWEEN date_add('{{ execution_date | cst_ds }}', -15) AND date_add('{{ execution_date | cst_ds }}', 5)
  and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
  AND is_pres_sign in ('0', '1')
group by date(plan_cainiao_platform_date),
         start_subordinate_agent_code, start_subordinate_agent_name,
         start_pick_network_code, start_pick_network_name
union all
SELECT sign_network_code                    as network_name
     , sign_network_name                    as network_code
     , sign_subordinate_agent_code          as agent_code
     , sign_subordinate_agent_name          as agent_name
     , 1                                  as port_type ---基础指标
     , SUM(cainiao_pres_platform_reach_sum) as reach_cnt
     , SUM(cainiao_pres_platform_total)     as all_cnt
     , date(plan_cainiao_platform_date)     as dt
from jms_dm.dm_net_pre_reach_rate_plan_dt
where date(plan_cainiao_platform_date) = '{{ execution_date | cst_ds }}'
  and dt BETWEEN date_add('{{ execution_date | cst_ds }}', -15) AND date_add('{{ execution_date | cst_ds }}', 5)
  and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
  AND is_pres_sign in ('0', '1')
group by date(plan_cainiao_platform_date),
         sign_subordinate_agent_code, sign_subordinate_agent_name,
         sign_network_code, sign_network_name
) detail left join jms_dim.dim_network_whole_massage net 
on detail.network_code = net.code
where dt = '{{ execution_date | cst_ds }}'
distribute by dt,1;
